package afcp.alumni.file;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import afcp.alumni.dao.MemberDao;
import afcp.alumni.dao.base.Dao.MemberProcessor;
import afcp.alumni.model.db.FileColumn;
import afcp.alumni.model.db.Member;
import afcp.alumni.model.db.Member.Role;
import afcp.alumni.model.db.Member.Sex;
import afcp.alumni.model.db.MemberUtils;
import afcp.alumni.model.db.MemberUtils.ColumnNumToValueConvertor;
import afcp.alumni.service.MemberService;

@Component
public class ExcelExportService implements ExportService {
	
	// For a new implementation building the xml on the fly to return chunks to
	// outputStream without using temporary disk files (forbidden by App
	// Engine), see:
	// http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi#answer-7281700

	private static final Log logger = LogFactory
			.getLog(ExcelExportService.class);

	@Inject
	private MemberService memberService;
	@Inject
	private MemberDao memberDao;

	@Override
	public void download(OutputStream outputStream) throws IOException {
		logger.info("Exporting the member list as Excel file...");
		buildExceDocumentInOnce(outputStream);
	}

	private void buildExceDocumentInOnce(OutputStream outputStream)
			throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();
		populateWorkbook(wb, outputStream);
	}

//	protected void buildExcelDocumentStream(OutputStream outputStream)
//			throws IOException {
//		SXSSFWorkbook wb = new SXSSFWorkbook(100);
//		populateWorkbook(wb, outputStream);
//		wb.dispose();
//	}

	private void populateWorkbook(Workbook wb, OutputStream outputStream)
			throws IOException {
		
		// Creates date formats:
		CreationHelper createHelper = wb.getCreationHelper();
		final CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
				"m/d/yy"));

		final Sheet sh = wb.createSheet();

		Row row = sh.createRow(0);
		
		List<FileColumn> columns = memberService.getAllFileColumns();
		
		for (FileColumn column : columns) {
			row.createCell(column.getPosition() - 1).setCellValue(
					column.getLabel());
		}
		
		// Prepare an object to get the member values faster. It converts a
		// FileColumn list (very hard to manipulate) into an object with a
		// convenient method, get(), that gives you the expected value for the
		// given column number.
		final ColumnNumToValueConvertor conv = new ColumnNumToValueConvertor(columns);
		

		memberDao.processAllMembers(new MemberProcessor<Member>() {
			private int i = 1;

			@Override
			public boolean process(List<Member> membersOfOnePage) {

				for (Member member : membersOfOnePage) {
					// Administrator is not exported.
					// If the administrator is also exported, when the excel
					// file is imported, there is a problem with passwords.
					if (!hasRole(member.getRoles(), Role.ADMIN)) {
						Row row = sh.createRow(i++);
						
						for (int col = 0; col <= conv.getLastCol(); col++) {
							Object val = conv.get(col, member);
							if (val != null) {
								if (val instanceof String) {
									row.createCell(col).setCellValue((String) val);
								} else if (val instanceof Number) {
									row.createCell(col).setCellValue(
											((Number) val).intValue());
								} else if (val instanceof Boolean) {
									row.createCell(col).setCellValue((Boolean) val);
								} else if (val instanceof Date) {
									setDate(row.createCell(col), (Date) val);
								} else if (val instanceof Sex) {
									row.createCell(col).setCellValue(
											MemberUtils.sexToString((Sex) val));
								} else {
									row.createCell(col).setCellValue(val.toString());
								}
							}
						}
					}
				}

				return true;
			}
			
			private boolean hasRole(List<Role> roles, Role roleId) {
				for (Role role : roles) {
					if (role == roleId) {
						return true;
					}
				}
				return false;
			}
			
			private void setDate(Cell cell, Date date) {

				if (date != null) {
					cell.setCellValue(date);
					cell.setCellStyle(cellStyle);
				}
			}
		});

		wb.write(outputStream);
	}

}
